package profile;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URL;
import java.util.Locale;

import javax.imageio.ImageIO;

import jxl.CellReferenceHelper;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;


/**
 * This class takes in a Profile and the original image filename. It then
 * exports it to an excel (.xls) file using the JXL api
 */
public class ProfileExporter implements Runnable {

    // These are used to convert from pixel size to Excel row/column size
    private static int CHART_IMAGE_ROWS = 35;
    private static int CHART_IMAGE_COLS = 12;
    
    private static int IMAGE_COL = 3;
    private static int C_COLUMN_WIDTH = 18; 

    // Various formats used when writing to spreadsheet
    WritableCellFormat numberFormat;
    WritableCellFormat croppedNumberFormat;
    WritableCellFormat regularFormat;
    WritableCellFormat regularFormatWrapped;
    WritableCellFormat headerFormat;
    WritableCellFormat highlightedFormat;
    WritableCellFormat boldFormat;

    private int currentMads = 0;
    private float dbThreshold;
    private File file;
    private JFreeChart chart;
    private long dRange = 0;
    private long numElts = 0;
    
    // Used to keep track of special columns and rows in the spreadsheet
    private int dipsCol = 3;
    private int minCol = 4;
    private int fwhmCol = 5;
    private int areaCol = 6;
    private int areaMedianCol = 7;
    private int areaElementsMedianCol = 8;
    private int dipDepthCol = 9;
    private int dipAreaCol = 10;
    private int dipWidthPercentageCol = 11;
    private int dipAreaPercentageCol = 12;
    private int dipAreaPercentageFromThreshold = 13;
    private int dipWidthCol = 14;
    private int dipAreaAdjusted4Col = 15;
    private int dipWidthAdjusted4Col = 16;
    private int dipAreaAdjusted6Col = 17;
    private int dipWidthAdjusted6Col = 18;
    
    private int dRangeCol = 3;
    private int dRangeRow = 0; //this gets set below
    private int numEltsCol = 3;
    private int numEltsRow = 0; //this gets set below

    private Profile profile;

    /**
     * Constructor takes in a profile and the original image file name
     * 
     * @param profile
     * @param originalFilename
     * @throws WriteException
     */
    public ProfileExporter(Profile profile, int currentMad, File file, JFreeChart chart,
            long dRange, long numEls, float dbThreshold) throws WriteException {
        this.profile = profile;
		this.currentMads = currentMad;
		this.file = file;
		this.chart = chart;
		this.dRange = dRange;
		this.numElts = numEls;
		this.dbThreshold = dbThreshold;
	}

    /**
     * Exports the profile to an Excel spreadsheet. Takes in the current number
     * of MADs selected by the user, the file to write the spreadsheet to, and
     * the JFreeChart for the profile.
     * 
     * @param currentMad
     *            Current number of MADs selected by the user
     * @param file
     *            The Excel spreadsheet file
     * @param chart
     * @throws WriteException
     * @throws IOException
     */
    @Override
    public void run() {
        
        try {
            // setup cell formats
            WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
            numberFormat = new WritableCellFormat(NumberFormats.FLOAT);
            croppedNumberFormat = new WritableCellFormat(NumberFormats.FLOAT);
            croppedNumberFormat.setBackground(Colour.GRAY_25);
            regularFormat = new WritableCellFormat(times10pt);
            regularFormatWrapped = new WritableCellFormat(times10pt);
            regularFormatWrapped.setWrap(true);
            highlightedFormat = new WritableCellFormat(NumberFormats.FLOAT);
            highlightedFormat.setBackground(Colour.PALE_BLUE);
            headerFormat = new WritableCellFormat(times10pt);
            headerFormat.setBorder(Border.BOTTOM, BorderLineStyle.MEDIUM);
            headerFormat.setWrap(true);
            WritableFont boldFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            boldFont.setBoldStyle(WritableFont.BOLD);
            boldFont.setUnderlineStyle(UnderlineStyle.NO_UNDERLINE);
            boldFont.setColour(Colour.BLACK);
            boldFormat = new WritableCellFormat(boldFont);
            
            WritableWorkbook workbook = createWorkbook(file);
            workbook.createSheet("Profile", 0);
    
            createContent(workbook, currentMads);
    
            workbook.write();
            workbook.close();
        } catch (Exception ex) {
            //run() doesn't allow checked exceptions so wrap it in
            //a runtime exception and rethrow it
            throw new RuntimeException(ex);
        }
    }

    /**
     * Creates workbook in file <code>file</code>
     * 
     * @param file
     * @return
     * @throws IOException
     */
    private WritableWorkbook createWorkbook(File file) throws IOException {
        WorkbookSettings wbSettings = new WorkbookSettings();
        wbSettings.setLocale(new Locale("en", "EN"));
        return Workbook.createWorkbook(file, wbSettings);
    }

    /**
     * Adds all of the data/statistics to the spreadsheet
     * 
     * @param workbook
     * @param currentMad
     * @param chartImageFile
     * @throws WriteException
     * @throws RowsExceededException
     * @throws IOException
     */
    private void createContent(WritableWorkbook workbook, int currentMad)
                                    throws WriteException, RowsExceededException, IOException {
        int row = 0;
        int col = 0;
        WritableSheet sheet = workbook.getSheet(0);
        for (int i = 0; i < profile.getArr().length; i++) {
            if (i <= profile.getCropInfo().left || i >= profile.getCropInfo().right){
                addNumber(profile.getArr()[i], col, i, sheet, croppedNumberFormat);
            } else {
                addNumber(profile.getArr()[i], col, i, sheet, numberFormat);
            }
        }

        col = 2;
        addCaption("Filename:", col, row, sheet, regularFormatWrapped);
        addCaption(profile.getOriginalImageFilename(), col + 1, row, sheet, regularFormat);
        row++;
        
        addCaption("Institution", col, row, sheet, regularFormatWrapped);
        addCaption("__________", col + 1, row, sheet, regularFormat);
        addCaption("Test Date", col + 2, row, sheet, regularFormatWrapped);
        addCaption("__________", col + 3, row, sheet, regularFormat);
        addCaption("Analaysis Date", col + 4, row, sheet, regularFormat);
        addCaption("__________", col + 5, row, sheet, regularFormat);
        row++;
        
        addCaption("Scanner model", col, row, sheet, regularFormatWrapped);
        addCaption("__________", col + 1, row, sheet, regularFormat);
        addCaption("S/N", col + 2, row, sheet, regularFormatWrapped);
        addCaption("_____", col + 3, row, sheet, regularFormat);
        row++;
        
        addCaption("Transd. Model", col, row, sheet, regularFormatWrapped);
        addCaption("__________", col + 1, row, sheet, regularFormat);
        addCaption("S/N", col + 2, row, sheet, regularFormatWrapped);
        addCaption("_____", col + 3, row, sheet, regularFormat);
        row++;
        
        addCaption("Uncompounded (Y/N)?", col, row, sheet, regularFormatWrapped);
        addCaption("____", col + 1, row, sheet, regularFormat);
        addCaption("Scanner Preset", col + 2, row, sheet, regularFormat);
        row++;
        
        addCaption("Settings: Gain", col, row, sheet, regularFormatWrapped);
        addCaption("_________", col + 1, row, sheet, regularFormat);
        addCaption("TGC", col + 2, row, sheet, regularFormatWrapped);
        addCaption("_________", col + 3, row, sheet, regularFormat);
        addCaption("Focal depth", col + 4, row, sheet, regularFormatWrapped);
        row++;
        
        addCaption("Other Settings:", col, row, sheet, regularFormatWrapped);
        row++;
        
        //set the C column width so the text is readable
        sheet.setColumnView(col, C_COLUMN_WIDTH);
        
        
        double mad = profile.getProfileMAD();
        addCaption("Dip Threshold (-MAD below median):", col, row, sheet, regularFormatWrapped);
        addNumber(profile.getProfileMedian() - (currentMads * mad), col + 1, row, sheet, regularFormat);
        
        
        double median = profile.getProfileMedian();
        row += 2;
        addCaption("For detailed explanations of the columns below, visit the project wiki page:", col, row, sheet, boldFormat);
        row++;
        URL url = new URL("https://code.google.com/p/um-qc/wiki/ProfileExcelFileDocumentation");
        WritableHyperlink wh = new WritableHyperlink(col, row, col + 5, row, url);
        sheet.addHyperlink(wh);
        row += 2;
        addCaption("MAD is defined as the median of the absolute deviations from the data's median.", col, row, sheet,
                regularFormat);
        row++;
        addCaption("MAD", col, row, sheet, regularFormat);
        addNumber(mad, col + 1, row, sheet, numberFormat);
        row++;
        addCaption("Median", col, row, sheet, regularFormat);
        addNumber(median, col + 1, row, sheet, numberFormat);
        row++;
        addCaption("MAD/Median", col, row, sheet, regularFormat);
        addNumber(mad / median, col + 1, row, sheet, numberFormat);
        row++;

        
        row++;
	    addCaption("Dynamic Range (dB)", dRangeCol - 1, row, sheet, regularFormat);
	    if (dRange > 0) {
	        addNumber(dRange, dRangeCol, row, sheet, numberFormat);
	    }
	    dRangeRow = row;
	    row++;
        addCaption("# elements:", numEltsCol - 1, row, sheet, regularFormat);
        addCaption("<-- Enter number of elements in array", numEltsCol + 1, row, sheet, boldFormat);
        
        numEltsRow = row;
        if (numElts > 0) {
        	addNumber(numElts, numEltsCol, row, sheet, numberFormat);
        }
        row++;
        addCaption("Horizontal scale (image pix/elt):", numEltsCol - 1, row, sheet, regularFormatWrapped);
        addFormula(profile.getImageWidth() + " / " + CellReferenceHelper.getColumnReference(numEltsCol) + (numEltsRow + 1), 
        			numEltsCol, row, sheet, numberFormat);
        row++;
        addCaption("Dip threshold (dB below median)", numEltsCol - 1, row, sheet, regularFormatWrapped);
        addNumber(dbThreshold, numEltsCol, row, sheet, numberFormat);
        row++;
        
        
        // add dips
        row++;
        addDipHeaders(row, sheet);
        
        row++;
        row = addDips(profile, sheet, dipsCol, row, currentMad);
        
        row++;
        row = addRegularProfileImages(sheet, row, currentMad);
        addSmoothedProfile(sheet, row, dipsCol, currentMad);
    }

    private void addDipHeaders(int row, WritableSheet sheet) throws RowsExceededException, WriteException {
        addCaption("Dips:", dipsCol, row, sheet, headerFormat);
        addCaption("Min", minCol, row, sheet, headerFormat);
        addCaption("Signal at half max", fwhmCol, row, sheet, headerFormat);
        addCaption("Area at FWHM", areaCol, row, sheet, headerFormat);
        addCaption("Area/median", areaMedianCol, row, sheet, headerFormat);
        addCaption("Area in elements/median", areaElementsMedianCol, row, sheet, headerFormat);
	    addCaption("Dip depth (dB)", dipDepthCol, row, sheet, headerFormat);
	    addCaption("Dip Area (dB elements)", dipAreaCol, row, sheet, headerFormat);
	    addCaption("Dip Width Percentage", dipWidthPercentageCol, row, sheet, headerFormat);
	    
	    if (dRange > 0) {

	        addCaption("Dip Area (dB - % of Profile)", dipAreaPercentageCol, row, sheet, headerFormat);
	        addCaption("Dip Area (dB - % of Profile from dB threshold)", dipAreaPercentageFromThreshold, row, sheet, headerFormat);
	        
	        if (numElts > 0) {
	            addCaption("Dip Width (elements)", dipWidthCol, row, sheet, headerFormat);
	            addCaption("Dip Area (threshold -4 dB from median)", dipAreaAdjusted4Col, row, sheet, headerFormat);
	            addCaption("Dip Width (elements) threshold -4 dB from median", dipWidthAdjusted4Col, row, sheet, headerFormat);
	            addCaption("Dip Area (threshold -6 dB from median)", dipAreaAdjusted6Col, row, sheet, headerFormat);
	            addCaption("Dip Width (elements) threshold -6 dB from median", dipWidthAdjusted6Col, row, sheet, headerFormat);
	        }
        }
    }
    
    private int addDips(Profile profile, WritableSheet sheet, int col, int row, int currentMad) throws RowsExceededException, WriteException {
        int dipNum = 1;
        for (Dip dip : profile.getDips(currentMad)) {
            addDip(sheet, col, row, dip, dipNum);
            dipNum++;
            row++;
        }
        return row;
    }

    /**
     * Adds the original profile chart and original median image with the selection drawn
     * 
     * @param sheet
     * @param curRow
     * @param currentMad
     * @param chartImageFile
     * @throws IOException
     */
    private int addRegularProfileImages(WritableSheet sheet, int curRow, int currentMad) throws IOException {
        BufferedImage bimg = profile.getImageWithSelection();
        File profileImg = bufferedImageToFile(bimg);
        
        File chartImageFile = File.createTempFile("chartImg", ".png");
        ChartUtilities.writeChartAsPNG(new FileOutputStream(chartImageFile), chart, 800, 600);
        sheet.addImage(new WritableImage(IMAGE_COL, curRow, CHART_IMAGE_COLS, CHART_IMAGE_ROWS, chartImageFile));
        sheet.addImage(new WritableImage(CHART_IMAGE_COLS + 4, curRow, bimg.getWidth() / 50, bimg.getHeight() / 12,
                profileImg));
        
        //We can delete the files once they are added to the spreadsheet
        profileImg.deleteOnExit();
        chartImageFile.deleteOnExit();
        
        //if the profile is cropped, add the original profile chart image
        if (profile.getCropInfo().left > 0 || profile.getCropInfo().right < profile.getWidth()) {
            curRow += CHART_IMAGE_ROWS + 5;
            addUncroppedProfileImage(sheet, curRow, currentMad);
        }

        curRow += CHART_IMAGE_ROWS + 5;
        return curRow;  
    }
    
    private void addSmoothedProfile(WritableSheet sheet, int row, int col, int currentMad) throws IOException, RowsExceededException, WriteException {
        SradProfile sProfile = createSradProfile(profile);
        
        addDipHeaders(row, sheet);
        row++;
        
        row = addDips(sProfile, sheet, col, row, currentMad);
        row++;
        
        addSmoothedProfileImage(sProfile, sheet, row, currentMad);
    }

    /**
     * Adds the Uncropped profile chart image to the spreadsheet
     * 
     * @param sheet
     * @param curRow
     * @param currentMad
     * @throws IOException
     * @throws FileNotFoundException
     */
    private void addUncroppedProfileImage(WritableSheet sheet, int curRow, int currentMad)
            throws IOException, FileNotFoundException {
        JFreeChart uncroppedChart = ProfileChartHelper.getProfileChart(profile, "Uncropped profile", currentMad, false);
        
        File uncroppedChartImageFile = File.createTempFile("uncroppedChartImg", ".png");
        ChartUtilities.writeChartAsPNG(new FileOutputStream(uncroppedChartImageFile), uncroppedChart, 800, 600);
        sheet.addImage(new WritableImage(IMAGE_COL, curRow, CHART_IMAGE_COLS, CHART_IMAGE_ROWS, uncroppedChartImageFile));
    }

    /**
     * Adds an image of the smoothed profile to the spreadsheet
     * 
     * @param sheet
     * @param col
     * @param row
     * @param madNumber
     * @throws FileNotFoundException
     * @throws IOException
     */
    private void addSmoothedProfileImage(SradProfile sProfile, WritableSheet sheet, int row, int madNumber)
            throws FileNotFoundException, IOException {
        JFreeChart chart = ProfileChartHelper.getProfileChart(sProfile, "Smoothed Profile (OPTIONAL)", madNumber, true);
        File chartImageFile = File.createTempFile("chartImg", ".png");
        ChartUtilities.writeChartAsPNG(new FileOutputStream(chartImageFile), chart, 800, 600);

        sheet.addImage(new WritableImage(IMAGE_COL, row, CHART_IMAGE_COLS, CHART_IMAGE_ROWS, chartImageFile));
    }
    
    private SradProfile createSradProfile(Profile profile) {
        double[] sradArr = new double[profile.arr.length];
        System.arraycopy(profile.arr, 0, sradArr, 0, sradArr.length);
        SradProfile sProfile = new SradProfile(sradArr);
        sProfile.applySrad();
        return sProfile;
    }

    /**
     * Adds all of the calculations for each dip highlighted in the profile
     * 
     * @param sheet
     * @param col
     * @param row
     * @param dip
     * @param dipNum
     * @throws RowsExceededException
     * @throws WriteException
     */
    private void addDip(WritableSheet sheet, int col, int row, Dip dip, int dipNum) throws RowsExceededException,
            WriteException {
        addNumber(dipNum, col, row, sheet, numberFormat);
        
        String numEltsCell = CellReferenceHelper.getColumnReference(numEltsCol) + (numEltsRow + 1);
        String dRangeCell = CellReferenceHelper.getColumnReference(dRangeCol) + (dRangeRow + 1);

        double mad = profile.getProfileMAD();
        double median = profile.getProfileMedian();
        double signalAtFWHM = median - (median - dip.getMin()) / 2;
        double area = dip.getAreaUnderFWHM(signalAtFWHM, median);
        double threshold = median - (currentMads * mad);
        double profileWidth = profile.getCropInfo().width;
        int width = dip.getWidthUnderValue(threshold);
        addNumber(dip.getMin(), minCol, row, sheet, numberFormat);
        addNumber(signalAtFWHM, fwhmCol, row, sheet, numberFormat);
        addNumber(area, areaCol, row, sheet, numberFormat);
        addNumber(area / median, areaMedianCol, row, sheet, numberFormat);
        addFormula((area / median) + " * " + numEltsCell + " / " + profileWidth, areaElementsMedianCol, row, sheet, numberFormat);
        
        //dip depth in db
        addFormula(dRangeCell + " * (" + (median - dip.getMin()) + ") / 256",
        		   dipDepthCol, row, sheet, numberFormat);
        
        //dip area in db
        addFormula(dip.getSummationUnderThreshold(median, threshold) + " * ( "+ dRangeCell + " / 256) * (" + numEltsCell + " / "+ profileWidth,
        		dipAreaCol, row, sheet, numberFormat);
            
        //dip width percentage  
        float widthPercentage = (float)dip.getWidth() / (float) profileWidth;
        addNumber(widthPercentage * 100.0f, dipWidthPercentageCol, row, sheet, numberFormat);
        
        if (dRange > 0) {
            
          //dip area in dB-Percentage of width
          addFormula("(100 * " + dRangeCell + " / 256) * (" + dip.getSummationUnderThreshold(median, threshold) + " / " + profileWidth, 
                  dipAreaPercentageCol, row, sheet, highlightedFormat);
          
          double medianInDb = (median / 256) * dRange;
          threshold = (medianInDb - dbThreshold) * (double)((double)256 / (double)dRange);
          
          //dip area in dB-Percentage of width under dB threshold
          double dbThresholdInPixels = (medianInDb - dbThreshold) * (256 / dRange);
          addNumber((100 * dRange / 256) * dip.getSummationUnderThreshold(median, dbThresholdInPixels) / profileWidth,
                  dipAreaPercentageFromThreshold, row, sheet, highlightedFormat);
            
            if (numElts > 0) {
    	        //dip width (elements)
    	        addFormula(width + " * (" + numEltsCell + " / " + profileWidth, 
    	        		dipWidthCol, row, sheet, numberFormat);
    	        
    	        //dip area, threshold adjusted -4 db
    	        threshold = (medianInDb - 4) * (double)((double)256 / (double)dRange);
    	        addNumber(dip.getDipAreaInDbElements(dRange, numElts, median, threshold, profileWidth), 
    	        		dipAreaAdjusted4Col, row, sheet, numberFormat);
    	        
    	        //dip width, threshold adjusted -4 db
    	        width = dip.getWidthUnderValue(threshold);
    	        addFormula(width + " * (" + numEltsCell + " / " + profileWidth, 
    	        		dipWidthAdjusted4Col, row, sheet, numberFormat);
    	        
    	        //dip area, threshold adjusted -6 db
    	        threshold = (medianInDb - 6) * (double)((double)256 / (double)dRange);
    	        addNumber(dip.getDipAreaInDbElements(dRange, numElts, median, threshold, profileWidth), 
    	        		dipAreaAdjusted6Col, row, sheet, numberFormat);
    	        
    	        //dip width, threshold adjusted -6 db
    	        width = dip.getWidthUnderValue(threshold);
    	        addFormula(width + " * (" + numEltsCell + " / " + profileWidth, 
    	        		dipWidthAdjusted6Col, row, sheet, numberFormat);
            }
        }
    }

    /**
     * Takes in a BufferedImage and writes it to a temporary file.  This is used to add images
     * to the spreadsheet because the JXL API only allows adding image files and not
     * BufferedImages
     * 
     * @param bimg
     * @return
     * @throws IOException
     */
    private static File bufferedImageToFile(BufferedImage bimg) throws IOException {
        File outputfile = File.createTempFile("tmpbimg", ".png");
        ImageIO.write(bimg, "png", outputfile);
        return outputfile;
    }

    private static void addNumber(double d, int column, int row, WritableSheet sheet, WritableCellFormat format)
            throws WriteException, RowsExceededException {
        Number number;
        number = new Number(column, row, d, format);
        sheet.addCell(number);
    }

    private static void addCaption(String s, int column, int row, WritableSheet sheet, WritableCellFormat format)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, format);
        sheet.addCell(label);
    }
    
    private void addFormula(String string, int col, int row, WritableSheet sheet,
            WritableCellFormat format) throws RowsExceededException, WriteException {
        Formula formula = new Formula(col, row, string, format);
        sheet.addCell(formula);
    }




}
